﻿Option Explicit On
Imports System.Drawing.Drawing2D
Imports System.IO
Imports System.Media
Imports System.Text
Imports DevComponents.DotNetBar.Controls
Imports DevComponents.DotNetBar.SuperGrid
Imports DevComponents.DotNetBar.SuperGrid.Style
Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Imports DevComponents.DotNetBar
Imports NCalc


Public Class frmview_voyage
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mysql1 As MySqlConnection = main_form.mysqlconection1
    Dim nickagent As String
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim inbtIndex As Integer

    Dim mySqlCommand1 As New MySqlCommand
    Dim mySqlAdaptor1 As New MySqlDataAdapter
    Dim mySqlReader1 As MySqlDataReader
    Public idvoymain As String
    Dim respone As Object
    Dim idvoyn As String
    Dim idvoys As String
    Public Shared idbooking As String
    Public Shared idborrow As String

    Public Shared idcontainer As String


    Dim ctnname As String
    Dim typectn As String

    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Dim sumint As Double
    Dim agentline As String

    Dim sumctn40 As Integer = 0
    Dim sumctn20 As Integer = 0
    Dim datecheck As String
    Dim stringDate1() As String
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()


    End Sub
    Private Sub frmview_voyage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        idvoymain = Format(frmsearch_voyage1.idvoyage, "000")

        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()
        txt_voyage.Text = Format(frmsearch_voyage1.idvoyage, "000")

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select VSSNAM,VSSMASTER,VSSNATION,VOYDATEEN,VOYDATEES from voyage join vesmain on voyage.VOYVESIDS = vesmain.VESMAINID where VOYAGEID ='" & frmsearch_voyage1.idvoyage & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                txt_vessel.Text = mySqlReader("VSSNAM")
                txt_namemaster.Text = CStr(mySqlReader("VSSMASTER"))
                txt_nationality.Text = mySqlReader("VSSNATION")
                txt_eta.Text = CStr(mySqlReader("VOYDATEEN"))
                txt_eta_penang.Text = mySqlReader("VOYDATEES")

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()



        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select BNO,BSHIPNAME,BFORWARDERNAME,BCONFIRMTIME,BCONFIRMDAY,BCTNTYPE,BCTNNO,BNICKAGENT,BOOKINGID,BCTNTYPE,BCTNNO,billbooking from booking left join booking_bl on booking_bl.idbooking_bl =  booking.BINV where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' order by  BCTNTYPE ASC , FIELD( BFORWARDERNAME ,'v.i.m','psk','queen','3gshipping' ,'baolidor','kvm','j.mac','topline') DESC;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("BNO"))


                    If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BSHIPNAME"))
                    End If

                    If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    Else

                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    End If

                    If mySqlReader("BCONFIRMDAY") Is DBNull.Value And mySqlReader("BCONFIRMTIME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BCONFIRMDAY") + " @" + mySqlReader("BCONFIRMTIME"))
                    End If

                    Dim array() As String
                    array = Split(mySqlReader("BCTNTYPE"), "'")

                    .SubItems.Add(mySqlReader("BCTNNO") + " X " + array(0) + "' " + mySqlReader("BNICKAGENT"))
                    .SubItems.Add(mySqlReader("BOOKINGID"))


                    If mySqlReader("BCTNTYPE") = "40'HC" Then
                        sumctn40 += CInt(mySqlReader("BCTNNO"))
                    ElseIf mySqlReader("BCTNTYPE") = "20'GP" Then
                        sumctn20 += CInt(mySqlReader("BCTNNO"))
                    End If
                    If mySqlReader("billbooking") Is DBNull.Value Then
                    Else
                        .SubItems.Add(mySqlReader("billbooking"))
                    End If

                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))


                End With



            End While


            TextBox1.Text = sumctn40.ToString + "  40'"
            TextBox2.Text = sumctn20.ToString + "  20'"
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()







    End Sub
    Public Sub saveall()

        If DataGridViewX1.Rows.Count > 0 Then
            For i = 0 To DataGridViewX1.Rows.Count - 1

                Dim check_count As Integer = 0
                Dim commandText3 As String
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE borrow SET COMNAME = '" & DataGridViewX1.Rows(i).Cells(12).Value & "',CARID = '" & DataGridViewX1.Rows(i).Cells(13).Value & "', TIMEDATE ='" & DataGridViewX1.Rows(i).Cells(4).Value & "' ,  TIMEHHMM = '" & DataGridViewX1.Rows(i).Cells(5).Value & "' , TIMEDATEIN ='" & DataGridViewX1.Rows(i).Cells(7).Value & "', TIMEHHMMIN ='" & DataGridViewX1.Rows(i).Cells(8).Value & "' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try


                Try
                    If DataGridViewX1.Rows(i).Cells(6).Value = True Then
                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='MS' ;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand.Connection = mysql
                        mySqlAdaptor.SelectCommand = mySqlCommand

                        Try
                            mySqlReader = mySqlCommand.ExecuteReader

                            While (mySqlReader.Read())

                                check_count = mySqlReader("ctncount")


                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()




                        If check_count > 0 Then

                        Else
                            Dim typectn As String
                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If
                            mySqlCommand.CommandText = "Select CTNSIZE,CTNAGENT from ctnmain where CTNMAINID ='" & DataGridViewX1.Rows(i).Cells(17).Value & "';"
                            ' mySqlCommand.CommandText = 
                            mySqlCommand.Connection = mysql
                            mySqlAdaptor.SelectCommand = mySqlCommand

                            Try
                                mySqlReader = mySqlCommand.ExecuteReader

                                While (mySqlReader.Read())
                                    If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                        typectn = "45G1"
                                    ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                        typectn = "22G1"
                                    End If

                                    agentline = Trim(mySqlReader("CTNAGENT"))

                                End While
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()

                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE ctnmain SET CTNSTAT ='2' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()









                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If


                            stringDate = Split(DataGridViewX1.Rows(i).Cells(3).Value, "-")
                            'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                            stringhour = Split(DataGridViewX1.Rows(i).Cells(4).Value, ":")
                            'MsgBox(stringhour(0) + stringhour(1))
                            sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                            mySqlCommand.Parameters.Clear()
                            Try
                                mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                                mySqlCommand.Connection = mysql
                                mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(i).Cells(0).Value)
                                mySqlCommand.Parameters.AddWithValue("@rptstat", "MS")

                                sumint = CDbl(sumtxt) + 100

                                mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                                mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                                mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                                mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(i).Cells(17).Value)
                                mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                                mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(i).Cells(4).Value)
                                mySqlCommand.ExecuteNonQuery()
                                mysql.Close()
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try


                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE borrow SET BRCHECK='1' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()






                        End If
                    End If
                    check_count = 0
                    If DataGridViewX1.Rows(i).Cells(9).Value = True Then

                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='FL' ;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand.Connection = mysql
                        mySqlAdaptor.SelectCommand = mySqlCommand

                        Try
                            mySqlReader = mySqlCommand.ExecuteReader

                            While (mySqlReader.Read())

                                check_count = mySqlReader("ctncount")


                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()

                        If check_count > 0 Then

                        Else
                            Dim typectn As String
                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If
                            mySqlCommand.CommandText = "Select CTNSIZE,CTNAGENT from ctnmain where CTNMAINID ='" & DataGridViewX1.Rows(i).Cells(17).Value & "';"
                            ' mySqlCommand.CommandText = 
                            mySqlCommand.Connection = mysql
                            mySqlAdaptor.SelectCommand = mySqlCommand

                            Try
                                mySqlReader = mySqlCommand.ExecuteReader

                                While (mySqlReader.Read())
                                    If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                        typectn = "45G1"
                                    ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                        typectn = "22G1"
                                    End If

                                    agentline = Trim(mySqlReader("CTNAGENT"))

                                End While
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()

                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE ctnmain SET CTNSTAT ='3' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()


                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If


                            stringDate = Split(DataGridViewX1.Rows(i).Cells(7).Value, "-")
                            'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                            stringhour = Split(DataGridViewX1.Rows(i).Cells(8).Value, ":")
                            'MsgBox(stringhour(0) + stringhour(1))
                            sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                            mySqlCommand.Parameters.Clear()
                            mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                            mySqlCommand.Connection = mysql
                            mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(i).Cells(0).Value)
                            mySqlCommand.Parameters.AddWithValue("@rptstat", "FL")

                            sumint = CDbl(sumtxt) + 100

                            mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                            mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                            mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                            mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(i).Cells(17).Value)
                            mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                            mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(i).Cells(7).Value)
                            mySqlCommand.ExecuteNonQuery()

                            mysql.Close()

                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE borrow SET BRCHECK1='1' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()




                        End If
                    End If

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If

                commandText3 = "UPDATE borrow SET BRBILLNAME='" & DataGridViewX1.Rows(i).Cells(14).Value & "' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()

                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Dim stringTBa As String
                If CInt(txt_voyage.Text) = 0 Then
                    stringTBa = "TBA"
                Else
                    stringTBa = CInt(txt_voyage.Text)
                End If
                commandText3 = "UPDATE ctnmain SET CTNWEIGHT ='" & DataGridViewX1.Rows(i).Cells(11).Value & "' , CTNVOYS ='" & txt_voyage.Text & "' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()



            Next




        End If



    End Sub
    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click




        mysql.Close()
        Dim remark1 As String
        Dim remark2 As String
        Dim checkupgrade As String
        Dim statctn As String

        Dim datetime As String
        Dim hhmm As String
        Dim datetime1 As String
        Dim hhmm1 As String

        Try
            idbooking = Trim(ListView1.SelectedItems(0).SubItems(5).Text)
        Catch ex As Exception

        End Try

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select CTNMARKSTRBOO,CTNMARKSEALBOO,CTNUPGRADE,CTNSTAT,BRCHECK,TIMEDATE,TIMEDATEIN,TIMEHHMM,TIMEHHMMIN,CTNSTRING,CTNSEALID,CTNAGENT,CTNVOYN,CTNWEIGHT,COMNAME,CARID,idborrow,CTNMAINID,BRCHECK1 from borrow join ctnmain on borrow.CTNID = ctnmain.CTNMAINID join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where BOOKID ='" & idbooking & "' order by idborrow ASC;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            'ListView2.Items.Clear()
            DataGridViewX1.Rows.Clear()
            While (mySqlReader.Read())


                If mySqlReader("CTNMARKSTRBOO") Is DBNull.Value Then
                    remark1 = "0"
                ElseIf mySqlReader("CTNMARKSTRBOO") = "1" Then
                    remark1 = "1"
                Else
                    remark1 = "0"
                End If


                If mySqlReader("CTNMARKSEALBOO") Is DBNull.Value Then
                    remark2 = "0"
                ElseIf mySqlReader("CTNMARKSEALBOO") = "1" Then
                    remark2 = "1"
                Else
                    remark2 = "0"
                End If

                If mySqlReader("CTNUPGRADE") Is DBNull.Value Then
                    checkupgrade = "N"
                ElseIf mySqlReader("CTNUPGRADE") = "U" Then
                    checkupgrade = "U"
                Else
                    checkupgrade = "N"
                End If

                'With ListView2.Items

                '    With .Add(mySqlReader("CTNSTRING"))
                '        If mySqlReader("CTNSEALID") Is DBNull.Value Then
                '            .SubItems.Add("-")
                '        Else

                '            .SubItems.Add(mySqlReader("CTNSEALID"))

                '        End If

                '        .SubItems.Add(mySqlReader("CTNSIZE"))
                '        .SubItems.Add(mySqlReader("CTNAGENT"))
                If mySqlReader("CTNSTAT") = "2" Then
                    statctn = "MS"

                ElseIf mySqlReader("CTNSTAT") = "3" Then
                    statctn = "FL"

                ElseIf mySqlReader("CTNSTAT") = "4" Then
                    statctn = "ST"

                ElseIf mySqlReader("CTNSTAT") = "1" Then
                    statctn = "MT"

                Else
                    statctn = ""
                End If
                Dim check1 As Boolean
                Dim check As Boolean
                If mySqlReader("BRCHECK") = 1 Then
                    check = True
                Else
                    check = False
                End If
                If mySqlReader("BRCHECK1") = 1 Then
                    check1 = True
                Else
                    check1 = False

                End If

                If Trim(mySqlReader("TIMEDATE").ToString) = "01-01-0001" Then
                    datetime = ""
                Else
                    datetime = mySqlReader("TIMEDATE")

                End If

                If Trim(mySqlReader("TIMEDATEIN").ToString) = "01-01-0001" Then
                    datetime1 = ""
                Else
                    datetime1 = mySqlReader("TIMEDATEIN")
                End If

                If Trim(mySqlReader("TIMEHHMM").ToString) = ":" Or Trim(mySqlReader("TIMEHHMM").ToString) = "00:00" Then
                    hhmm = ""
                Else
                    hhmm = mySqlReader("TIMEHHMM")
                End If


                If Trim(mySqlReader("TIMEHHMMIN").ToString) = ":" Or Trim(mySqlReader("TIMEHHMM").ToString) = "00:00" Then
                    hhmm1 = ""
                Else
                    hhmm1 = mySqlReader("TIMEHHMMIN")
                End If





                '        .SubItems.Add(mySqlReader("idborrow"))
                '        .SubItems.Add(mySqlReader("CTNMAINID"))
                '        datecheck = mySqlReader("VOYDATEEN")
                '        stringDate1 = Split(datecheck, "-")
                '        .SubItems.Add(stringDate1(2) + stringDate1(1) + stringDate1(0))
                '    End With


                DataGridViewX1.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNSEALID"), mySqlReader("CTNAGENT"), "N" + mySqlReader("CTNVOYN").ToString, datetime, hhmm, check, datetime1, hhmm1, check1, statctn, mySqlReader("CTNWEIGHT"), mySqlReader("COMNAME"), mySqlReader("CARID"), checkupgrade, "", mySqlReader("idborrow"), mySqlReader("CTNMAINID"), remark1, remark2})



                'End With



            End While
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If




            For i As Integer = 0 To DataGridViewX1.Rows.Count - 1

                If DataGridViewX1.Rows(i).Cells(6).Value = True And DataGridViewX1.Rows(i).Cells(9).Value = False Then

                    'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                    'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                    DataGridViewX1.Rows(i).Cells(10).Style.BackColor = Color.DarkOrange
                ElseIf DataGridViewX1.Rows(i).Cells(6).Value = True And DataGridViewX1.Rows(i).Cells(9).Value = True Then

                    DataGridViewX1.Rows(i).Cells(10).Style.BackColor = Color.DeepSkyBlue

                End If


                If DataGridViewX1.Rows(i).Cells(18).Value = "1" Then
                    DataGridViewX1.Rows(i).Cells(0).Style.BackColor = Color.DarkOrange
                End If


                If DataGridViewX1.Rows(i).Cells(19).Value = "1" Then
                    DataGridViewX1.Rows(i).Cells(1).Style.BackColor = Color.DarkOrange
                End If



                If DataGridViewX1.Rows(i).Cells(14).Value = "U" Then
                    DataGridViewX1.Rows(i).Cells(2).Style.BackColor = Color.DeepSkyBlue
                End If
            Next

            'Dim resultd As Integer
            'ListView2.View = View.Details
            'ListView2.GridLines = True
            'For i As Integer = 0 To ListView2.Items.Count - 1 Step 1 'Code การสลับสีใน listview
            '    resultd = CInt(Date.Now.ToString("yyyyMMdd")) - CInt(ListView2.Items(i).SubItems(7).Text)
            '    If resultd > 8 Then
            '        ListView2.Items(i).BackColor = Color.Red
            '    Else
            '        ListView2.Items(i).BackColor = Color.White
            '    End If
            'Next i

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


    End Sub
    Public Sub loaddatagrid()

        mysql.Close()
        Dim remark1 As String
        Dim remark2 As String
        Dim checkupgrade As String
        Dim statctn As String

        Dim datetime As String
        Dim hhmm As String
        Dim datetime1 As String
        Dim hhmm1 As String

        Try
            idbooking = Trim(ListView1.SelectedItems(0).SubItems(5).Text)
        Catch ex As Exception

        End Try

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select CTNMARKSTRBOO,CTNMARKSEALBOO,CTNUPGRADE,CTNSTAT,BRCHECK,TIMEDATE,TIMEDATEIN,TIMEHHMM,TIMEHHMMIN,CTNSTRING,CTNSEALID,CTNAGENT,CTNVOYN,CTNWEIGHT,COMNAME,CARID,idborrow,CTNMAINID,BRCHECK1 from borrow join ctnmain on borrow.CTNID = ctnmain.CTNMAINID join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where BOOKID ='" & idbooking & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            'ListView2.Items.Clear()
            DataGridViewX1.Rows.Clear()
            While (mySqlReader.Read())


                If mySqlReader("CTNMARKSTRBOO") Is DBNull.Value Then
                    remark1 = "0"
                ElseIf mySqlReader("CTNMARKSTRBOO") = "1" Then
                    remark1 = "1"
                Else
                    remark1 = "0"
                End If


                If mySqlReader("CTNMARKSEALBOO") Is DBNull.Value Then
                    remark2 = "0"
                ElseIf mySqlReader("CTNMARKSEALBOO") = "1" Then
                    remark2 = "1"
                Else
                    remark2 = "0"
                End If

                If mySqlReader("CTNUPGRADE") Is DBNull.Value Then
                    checkupgrade = "N"
                ElseIf mySqlReader("CTNUPGRADE") = "U" Then
                    checkupgrade = "U"
                Else
                    checkupgrade = "N"
                End If

                'With ListView2.Items

                '    With .Add(mySqlReader("CTNSTRING"))
                '        If mySqlReader("CTNSEALID") Is DBNull.Value Then
                '            .SubItems.Add("-")
                '        Else

                '            .SubItems.Add(mySqlReader("CTNSEALID"))

                '        End If

                '        .SubItems.Add(mySqlReader("CTNSIZE"))
                '        .SubItems.Add(mySqlReader("CTNAGENT"))
                If mySqlReader("CTNSTAT") = "2" Then
                    statctn = "MS"

                ElseIf mySqlReader("CTNSTAT") = "3" Then
                    statctn = "FL"

                ElseIf mySqlReader("CTNSTAT") = "4" Then
                    statctn = "ST"

                ElseIf mySqlReader("CTNSTAT") = "1" Then
                    statctn = "MT"

                Else
                    statctn = ""
                End If
                Dim check1 As Boolean
                Dim check As Boolean
                If mySqlReader("BRCHECK") = 1 Then
                    check = True
                Else
                    check = False
                End If
                If mySqlReader("BRCHECK1") = 1 Then
                    check1 = True
                Else
                    check1 = False

                End If

                If Trim(mySqlReader("TIMEDATE").ToString) = "01-01-0001" Then
                    datetime = ""
                Else
                    datetime = mySqlReader("TIMEDATE")

                End If

                If Trim(mySqlReader("TIMEDATEIN").ToString) = "01-01-0001" Then
                    datetime1 = ""
                Else
                    datetime1 = mySqlReader("TIMEDATEIN")
                End If

                If Trim(mySqlReader("TIMEHHMM").ToString) = ":" Or Trim(mySqlReader("TIMEHHMM").ToString) = "00:00" Then
                    hhmm = ""
                Else
                    hhmm = mySqlReader("TIMEHHMM")
                End If


                If Trim(mySqlReader("TIMEHHMMIN").ToString) = ":" Or Trim(mySqlReader("TIMEHHMM").ToString) = "00:00" Then
                    hhmm1 = ""
                Else
                    hhmm1 = mySqlReader("TIMEHHMMIN")
                End If





                '        .SubItems.Add(mySqlReader("idborrow"))
                '        .SubItems.Add(mySqlReader("CTNMAINID"))
                '        datecheck = mySqlReader("VOYDATEEN")
                '        stringDate1 = Split(datecheck, "-")
                '        .SubItems.Add(stringDate1(2) + stringDate1(1) + stringDate1(0))
                '    End With


                DataGridViewX1.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNSEALID"), mySqlReader("CTNAGENT"), "N" + mySqlReader("CTNVOYN").ToString, datetime, hhmm, check, datetime1, hhmm1, check1, statctn, mySqlReader("CTNWEIGHT"), mySqlReader("COMNAME"), mySqlReader("CARID"), checkupgrade, "", mySqlReader("idborrow"), mySqlReader("CTNMAINID"), remark1, remark2})



                'End With

                'End With
            End While
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
     

        For i As Integer = 0 To DataGridViewX1.Rows.Count - 1

            If DataGridViewX1.Rows(i).Cells(6).Value = True And DataGridViewX1.Rows(i).Cells(9).Value = False Then

                'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                DataGridViewX1.Rows(i).Cells(10).Style.BackColor = Color.DarkOrange
            ElseIf DataGridViewX1.Rows(i).Cells(6).Value = True And DataGridViewX1.Rows(i).Cells(9).Value = True Then

                DataGridViewX1.Rows(i).Cells(10).Style.BackColor = Color.DeepSkyBlue

            End If


            If DataGridViewX1.Rows(i).Cells(18).Value = "1" Then
                DataGridViewX1.Rows(i).Cells(0).Style.BackColor = Color.DarkOrange
            End If


            If DataGridViewX1.Rows(i).Cells(19).Value = "1" Then
                DataGridViewX1.Rows(i).Cells(1).Style.BackColor = Color.DarkOrange
            End If



            If DataGridViewX1.Rows(i).Cells(14).Value = "U" Then
                DataGridViewX1.Rows(i).Cells(2).Style.BackColor = Color.DeepSkyBlue
            End If
        Next

    End Sub
    Private Sub ButtonItem7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If ListView1.SelectedItems.Count > 0 Then
            idbooking = ListView1.SelectedItems(0).SubItems(5).Text
            Dim nextform As frmrpt_bl1 = New frmrpt_bl1
            nextform.Show()

        End If

    End Sub
    Private Sub ButtonItem8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem8.Click

        Dim nextform As frmset_bl = New frmset_bl
        nextform.Show()


    End Sub
    Private Sub ButtonItem11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem11.Click

        If DataGridViewX1.Rows.Count > 0 Then
            Dim nextform As frmrpt_gateout = New frmrpt_gateout
            nextform.Show()
            'updateContainer()

        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")

        End If




    End Sub
    Private Sub ButtonItem12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem12.Click

        If DataGridViewX1.Rows.Count > 0 Then
            Dim nextform As frmrpt_gatin = New frmrpt_gatin
            nextform.Show()
        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")
        End If


    End Sub
    Public Sub updateContainer()


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        Dim commandText3 As String
        commandText3 = "UPDATE ctnmain SET CTNSTAT = '2' where CTNMAINID = '" & idcontainer & "';"


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()



    End Sub
    Private Sub ButtonItem9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem9.Click
        If ListView1.SelectedItems.Count > 0 Then
            If dateconfirm.Text <> "" AndAlso MaskedTextBoxAdv1.Text <> "" Then
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    Dim commandText2 As String
                    commandText2 = "UPDATE booking SET BCONFIRMDAY = '" & dateconfirm.Text & "',BCONFIRMTIME ='" & MaskedTextBoxAdv1.Text & "'  WHERE BOOKINGID = '" & ListView1.SelectedItems(0).SubItems(5).Text & "'; "
                    mySqlCommand.CommandText = commandText2
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()

                    MsgBox("แก้ไขข้อเรียบร้อย")

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End If


        Else
            MsgBox("กรุณาเลือก Booking เพื่อทำการ Comfirm วัน Close date")
        End If
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select BNO,BSHIPNAME,BFORWARDERNAME,BCONFIRMTIME,BCONFIRMDAY,BCTNTYPE,BCTNNO,BNICKAGENT,BOOKINGID,BCTNTYPE,BCTNNO,billbooking from booking left join booking_bl on booking_bl.idbooking_bl =  booking.BINV where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' order by  BCTNTYPE ASC , FIELD( BFORWARDERNAME ,'v.i.m','psk','queen','3gshipping' ,'baolidor','kvm','j.mac','topline') DESC;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("BNO"))


                    If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BSHIPNAME"))
                    End If

                    If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    Else

                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    End If

                    If mySqlReader("BCONFIRMDAY") Is DBNull.Value And mySqlReader("BCONFIRMTIME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BCONFIRMDAY") + " @" + mySqlReader("BCONFIRMTIME"))
                    End If
                    .SubItems.Add(mySqlReader("BCTNNO") + " X " + mySqlReader("BCTNTYPE") + "   " + mySqlReader("BNICKAGENT"))
                    .SubItems.Add(mySqlReader("BOOKINGID"))


                    If mySqlReader("BCTNTYPE") = "40'HC" Then
                        sumctn40 += CInt(mySqlReader("BCTNNO"))
                    ElseIf mySqlReader("BCTNTYPE") = "20'GP" Then
                        sumctn20 += CInt(mySqlReader("BCTNNO"))
                    End If
                    If mySqlReader("billbooking") Is DBNull.Value Then
                    Else
                        .SubItems.Add(mySqlReader("billbooking"))
                    End If
                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))


                End With


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Private Sub ButtonItem5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem5.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelReport))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try
        End If

    End Sub
    Private Sub excelReport()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Name = "S" + txt_voyage.Text
        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape


            Try
                .Shapes.AddPicture("\\ykpserver55\DB\movement.png", False, True, 1, 1, 150, 80)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 2.75
            .Range("B1").ColumnWidth = 14.25
            .Range("C1").ColumnWidth = 12.63
            .Range("D1").ColumnWidth = 12
            .Range("E1").ColumnWidth = 14.25
            .Range("F1").ColumnWidth = 8.38
            .Range("G1").ColumnWidth = 17.63
            .Range("H1").ColumnWidth = 15.6
            .Range("I1").ColumnWidth = 9.75
            .Range("J1").ColumnWidth = 10.5
            .Range("K1").ColumnWidth = 19.38
            .Range("L1").ColumnWidth = 13.5
            .Range("M1").ColumnWidth = 16.25
            .Range("N1").ColumnWidth = 12.38

            .Range("A1:M500").Font.Name = "Tahoma"
            .Range("A1:M500").Font.Size = 10

            .Rows("1:500").rowheight = 21
            With .Range("E1:E1")
                .Merge()
                .Value = "VESSEL :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F1:H1")
                .Merge()
                .Value = txt_vessel.Text
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft

            End With
            With .Range("E2:E2")
                .Merge()
                .Value = "VOYAGE :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F2:H2")
                .Merge()
                .Value = "S" + txt_voyage.Text
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E3:E3")
                .Merge()
                .Value = "PORT OF LOADING : "
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F3:H3")
                .Merge()
                .Value = " YUSOB INTERNATIONAL KANTANG PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E4:E4")
                .Merge()
                .Value = "SAIL DATE :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F4:H4")
                .Merge()
                .NumberFormat = "B1d-mmm-yy"
                .Value = Date.Now.Date.ToString("dd-MMM-yyyy")
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from voyage join vesmain on voyage.VOYVESIDS = vesmain.VESMAINID join booking on booking.BVOYAGE = voyage.VOYAGEID left join  booking_bl on booking_bl.booking_id = booking.BOOKINGID   where VOYAGEID ='" & frmsearch_voyage1.idvoyage & "'order by  BCTNTYPE ASC , FIELD( BFORWARDERNAME ,'v.i.m','psk','queen','3gshipping' ,'baolidor','j.mac','skr','kvm','YKP GYPSUM' ) DESC;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Dim number1 As Integer = 0
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    mysql1.Close()
                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If
                    mySqlCommand1.CommandText = "Select count(idbooking_bl_ctn) as sum1 from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "' ;"
                    ' mySqlCommand.CommandText = 
                    mySqlCommand1.Connection = mysql1
                    mySqlAdaptor1.SelectCommand = mySqlCommand1

                    Try
                        mySqlReader1 = mySqlCommand1.ExecuteReader

                        While (mySqlReader1.Read())
                            number1 = mySqlReader1("sum1")
                        End While
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql1.Close()

                    If number1 = 0 Then
                        With .Range("A" + count.ToString + ":C" + count.ToString)
                            .Merge()
                            .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                            .Characters(1, 7).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("D" + count.ToString + ":E" + count.ToString)
                            .Merge()
                            .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                            .Characters(1, 9).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("G" + count.ToString + ":G" + count.ToString)
                            .Merge()
                            .Value = "MOTHER VESL : "
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With

                        count += 1
                        With .Range("G" + count.ToString + ":G" + count.ToString)
                            .Merge()
                            .Value = "SCN : "
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("H" + count.ToString + ":H" + count.ToString)
                            .Merge()
                            .Value = mySqlReader("BSCN")
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("A" + count.ToString + ":C" + count.ToString)
                            .Merge()
                            .Value = "BOOKING NO. : " + mySqlReader("BNO")
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With

                        If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                            With .Range("D" + count.ToString + ":E" + count.ToString)
                                .Merge()
                                .Value = "INV.NO.: " + mySqlReader("billbooking")

                                .Font.Bold = True
                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlLeft
                            End With

                        End If

                        count += 1
                        With .Range("A" + count.ToString + ":E" + count.ToString)
                            .Merge()
                            .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                            .Characters(1, 6).Font.Bold = True

                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("G" + count.ToString + ":G" + count.ToString)
                            .Merge()
                            .Value = "PORT OF TRANSSHIP : "
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        count += 1
                        Dim typestring() As String
                        typestring = Split(mySqlReader("BCTNTYPE"), "'")
                        With .Range("A" + count.ToString + ":E" + count.ToString)
                            .Merge()
                            .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                            .Characters(1, 30).Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("G" + count.ToString + ":G" + count.ToString)
                            .Merge()
                            .Value = "PORT OF DISCHARGE : "
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With

                        count += 1
                        'End If
                        With .Range("A" + count.ToString + ":E" + count.ToString)
                            .Merge()

                            .Value = "COMMODITY :" + mySqlReader("BCOM")
                            .Characters(1, 11).Font.Bold = True

                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                        With .Range("G" + count.ToString + ":G" + count.ToString)
                            .Merge()
                            .Value = "PORT OF DELIVERY : "
                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With

                        With .Range("K" + count.ToString + ":M" + count.ToString)
                            .Merge()

                            If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                            Else
                                .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                            End If

                            .Font.Bold = True
                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                            .HorizontalAlignment = Excel.Constants.xlLeft
                        End With
                    ElseIf number1 = 7 Then

                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 2 Then


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 3 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 4 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 5 Then

                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1



                                ElseIf x = 6 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("K" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1

                                ElseIf x = 0 Then
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    count += 1

                                ElseIf x = 1 Then
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1
                                End If

                                x += 1



                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql1.Close()













                    ElseIf number1 = 6 Then

                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 1 Then


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 2 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 3 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 4 Then

                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1



                                ElseIf x = 5 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("K" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                ElseIf x = 0 Then
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1
                                End If
                                x += 1



                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql1.Close()




                    ElseIf number1 = 5 Then
                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 0 Then
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    count += 1



                                ElseIf x = 1 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 2 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 3 Then

                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1



                                ElseIf x = 4 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("K" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                End If
                                x += 1


                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql1.Close()


                    ElseIf number1 = 4 Then


                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 0 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1


                                ElseIf x = 1 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 2 Then

                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1



                                ElseIf x = 3 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("K" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                End If
                                x += 1
                            End While
                        Catch ex As Exception

                        End Try


                    ElseIf number1 = 3 Then


                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 0 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1


                                ElseIf x = 1 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1

                                ElseIf x = 2 Then

                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1


                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("K" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                End If
                                x += 1
                            End While
                        Catch ex As Exception

                        End Try


                    ElseIf number1 = 2 Then

                        Dim nodb As String
                        Dim countstring As String
                        Dim countnumber As Integer

                        Dim x As Integer = 0
                        mysql1.Close()
                        If mysql1.State = ConnectionState.Closed Then
                            mysql1.Open()
                        End If
                        mySqlCommand1.CommandText = "Select * from booking_bl_ctn where bookid ='" & mySqlReader("BOOKINGID") & "'  order by idnumber ASC;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand1.Connection = mysql1
                        mySqlAdaptor1.SelectCommand = mySqlCommand1

                        Try
                            mySqlReader1 = mySqlCommand1.ExecuteReader

                            While (mySqlReader1.Read())
                                If x = 0 Then

                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "SHIPPER : " + mySqlReader("BNSHIPNAME")
                                        .Characters(1, 7).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("D" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "FORWARDER : " + mySqlReader("BNFORWARDERNAME")
                                        .Characters(1, 9).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb
                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1


                                    With .Range("A" + count.ToString + ":C" + count.ToString)
                                        .Merge()
                                        .Value = "BOOKING NO. : " + mySqlReader("BNO")
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    If mySqlReader("billbooking") IsNot DBNull.Value And Trim(mySqlReader("billbooking").ToString).Length > 3 Then

                                        With .Range("D" + count.ToString + ":E" + count.ToString)
                                            .Merge()
                                            .Value = "INV.NO.: " + mySqlReader("billbooking")

                                            .Font.Bold = True
                                            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                            .HorizontalAlignment = Excel.Constants.xlLeft
                                        End With

                                    End If

                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        .Value = "SCN : "
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("H" + count.ToString + ":H" + count.ToString)
                                        .Merge()
                                        .Value = mySqlReader("BSCN")
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1


                                ElseIf x = 1 Then
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VESSEL : " + txt_vessel.Text + "  VOYAGE : S" + txt_voyage.Text
                                        .Characters(1, 6).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With


                                    With .Range("G" + count.ToString + ":G" + count.ToString)
                                        .Merge()
                                        If mySqlReader1("name_2") Is DBNull.Value Then
                                            nodb = ""
                                        Else
                                            nodb = mySqlReader1("name_2")
                                        End If
                                        .Value = mySqlReader1("name_1") + " : " + nodb

                                        countnumber = mySqlReader1("name_1").ToString.Length
                                        .Characters(1, countnumber).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    count += 1


                                    Dim typestring() As String
                                    typestring = Split(mySqlReader("BCTNTYPE"), "'")
                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()
                                        .Value = "VOLUME / CONTAINER TYPE SIZE :   " + mySqlReader("BCTNNO") + " X " + typestring(0) + "'  " + mySqlReader("BNICKAGENT")
                                        .Characters(1, 30).Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With

                                    count += 1


                                    With .Range("A" + count.ToString + ":E" + count.ToString)
                                        .Merge()

                                        .Value = "COMMODITY :" + mySqlReader("BCOM")
                                        .Characters(1, 11).Font.Bold = True

                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                    With .Range("L" + count.ToString + ":M" + count.ToString)
                                        .Merge()
                                        If mySqlReader("BCONFIRMDAY") = "TBA" Then
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY")
                                        Else
                                            .Value = "CLOSE DATE :   " + mySqlReader("BCONFIRMDAY") + "  @" + mySqlReader("BCONFIRMTIME")
                                        End If
                                        .Font.Bold = True
                                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                        .HorizontalAlignment = Excel.Constants.xlLeft
                                    End With
                                End If
                                x += 1
                            End While
                        Catch ex As Exception

                        End Try



                    End If




                    count += 1
                    For J = 7 To 10
                        .Range("A" + count.ToString + ":A" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("B" + count.ToString + ":B" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("C" + count.ToString + ":C" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("D" + count.ToString + ":D" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("E" + count.ToString + ":E" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("F" + count.ToString + ":F" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("G" + count.ToString + ":G" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("H" + count.ToString + ":H" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("I" + count.ToString + ":I" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("J" + count.ToString + ":J" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("K" + count.ToString + ":K" + count.ToString).Borders(J).Weight = 2 ' xlThin
                        .Range("L" + count.ToString + ":L" + count.ToString).Borders(J).Weight = 2 ' xlThin

                        .Range("M" + count.ToString + ":N" + count.ToString).Borders(J).Weight = 2 ' xlThin
                    Next
                    With .Range("A" + count.ToString + ":A" + count.ToString)
                        .Merge()
                        .Value = "NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("B" + count.ToString + ":B" + count.ToString)
                        .Merge()
                        .Value = "CONTAINER NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("C" + count.ToString + ":C" + count.ToString)
                        .Merge()
                        .Value = "SEAL NO."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("D" + count.ToString + ":D" + count.ToString)
                        .Merge()
                        .Value = "LINER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("E" + count.ToString + ":E" + count.ToString)
                        .Merge()
                        .Value = "DATE IN"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("F" + count.ToString + ":F" + count.ToString)
                        .Merge()
                        .Value = "VOY."
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("G" + count.ToString + ":G" + count.ToString)
                        .Merge()
                        .Value = "GET OUT(EMPTY)"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("H" + count.ToString + ":H" + count.ToString)
                        .Merge()
                        .Value = "GET IN(LADEN)"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("I" + count.ToString + ":I" + count.ToString)
                        .Merge()
                        .Value = "DATE OUT"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("J" + count.ToString + ":J" + count.ToString)
                        .Merge()
                        .Value = "WEIGHT"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("K" + count.ToString + ":K" + count.ToString)
                        .Merge()
                        .Value = "SHIPPER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("L" + count.ToString + ":L" + count.ToString)
                        .Merge()
                        .Value = "FORWARDER"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("M" + count.ToString + ":N" + count.ToString)
                        .Merge()
                        .Value = "TRANSPORT BY"
                        .Font.Bold = True
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    count += 1
                    nickagent = mySqlReader("BNICKAGENT")
                    mysql1.Close()
                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If
                    mySqlCommand1.CommandText = "Select * from borrow join booking on borrow.BOOKID= booking.BOOKINGID join ctnmain on ctnmain.CTNMAINID = borrow.CTNID join voyage  on ctnmain.CTNVOYN = voyage.VOYAGEID   where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' and BOOKID='" & mySqlReader("BOOKINGID") & "';"
                    ' mySqlCommand.CommandText = 
                    mySqlCommand1.Connection = mysql1
                    mySqlAdaptor1.SelectCommand = mySqlCommand1

                    Try
                        mySqlReader1 = mySqlCommand1.ExecuteReader
                        Dim number As Integer = 1
                        While (mySqlReader1.Read())

                            For J = 7 To 8
                                .Range("A" + count.ToString + ":A" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("B" + count.ToString + ":B" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("C" + count.ToString + ":C" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("D" + count.ToString + ":D" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("E" + count.ToString + ":E" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("F" + count.ToString + ":F" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("G" + count.ToString + ":G" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("H" + count.ToString + ":H" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("I" + count.ToString + ":I" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("J" + count.ToString + ":J" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("K" + count.ToString + ":K" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("L" + count.ToString + ":L" + count.ToString).Borders(J).Weight = 2 ' xlThin
                                .Range("M" + count.ToString + ":M" + count.ToString).Borders(J).Weight = 2
                                .Range("N" + count.ToString + ":N" + count.ToString).Borders(J).Weight = 2
                            Next
                            .Range("O" + count.ToString + ":O" + count.ToString).Borders(7).Weight = 2
                            With .Range("A" + count.ToString + ":A" + count.ToString)
                                .Merge()
                                .Value = number

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            number += 1
                            With .Range("B" + count.ToString + ":B" + count.ToString)
                                .Merge()

                                .Value = mySqlReader1("CTNSTRING")


                                If mySqlReader1("CTNUPGRADE") Is DBNull.Value Then
                                Else
                                    If mySqlReader1("CTNUPGRADE") = "U" Then
                                        .Font.Color = Color.Red
                                    End If
                                End If

                                If mySqlReader1("CTNMARKSTR") Is DBNull.Value Then
                                Else
                                    If mySqlReader1("CTNMARKSTR").ToString.Length > 3 Then
                                        .AddComment(mySqlReader1("CTNMARKSTR").ToString)
                                        .Interior.Color = Excel.XlRgbColor.rgbYellow
                                    End If
                                End If
                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("C" + count.ToString + ":C" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CTNSEALID")
                                If mySqlReader1("CTNMARKSEAL") Is DBNull.Value Then
                                Else
                                    If mySqlReader1("CTNMARKSEAL").ToString.Length > 3 Then
                                        .AddComment(mySqlReader1("CTNMARKSEAL").ToString)
                                        .Interior.Color = Excel.XlRgbColor.rgbYellow
                                    End If
                                End If

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("D" + count.ToString + ":D" + count.ToString)
                                .Merge()
                                .Value = nickagent

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("E" + count.ToString + ":E" + count.ToString)
                                .Merge()
                                .NumberFormat = "@"
                                .Value = mySqlReader1("VOYDATEEN")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("F" + count.ToString + ":F" + count.ToString)
                                .Merge()
                                .Value = "N" + Format(mySqlReader1("CTNVOYN"), "000")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("G" + count.ToString + ":G" + count.ToString)
                                .Merge()
                                If mySqlReader1("TIMEDATE") Is DBNull.Value Or mySqlReader1("TIMEHHMM") Is DBNull.Value Then
                                Else
                                    .Value = mySqlReader1("TIMEDATE") + "/" + mySqlReader1("TIMEHHMM")
                                End If

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("H" + count.ToString + ":H" + count.ToString)
                                .Merge()
                                If mySqlReader1("TIMEDATEIN") Is DBNull.Value Or mySqlReader1("TIMEHHMMIN") Is DBNull.Value Then
                                Else
                                    .Value = mySqlReader1("TIMEDATEIN") + "/" + mySqlReader1("TIMEHHMMIN")
                                End If

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("I" + count.ToString + ":I" + count.ToString)
                                .Merge()
                                .NumberFormat = "@"
                                .Value = MaskedTextBoxAdv2.Text


                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With



                            With .Range("J" + count.ToString + ":J" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CTNWEIGHT")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With

                            With .Range("K" + count.ToString + ":K" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("BNSHIPNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("L" + count.ToString + ":L" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("BNFORWARDERNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("M" + count.ToString + ":M" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("COMNAME")

                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            With .Range("N" + count.ToString + ":N" + count.ToString)
                                .Merge()
                                .Value = mySqlReader1("CARID")
                                .Font.Size = 10
                                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                                .HorizontalAlignment = Excel.Constants.xlCenter
                            End With
                            count += 1
                        End While
                        .Range("A" + count.ToString + ":N" + count.ToString).Borders(8).Weight = 2 ' xlThin
                        number = 0
                        mysql1.Close()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    count += 1
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
                        mysql.Close()



        End With

        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False

        Try
            excelbooks.SaveAs(pathExcel.ToString + "\ContainerMovement S" + txt_voyage.Text + ".xlsx")

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next
            MsgBox("Report Complete")


        Catch ex As Exception

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next


        End Try
        showResult(200)
        CircularProgress1.IsRunning = False
    End Sub
    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else
            CircularProgress1.IsRunning = False

        End If
    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Dim cf As New frmsearch_voyage1
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub ButtonItem14_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem14.Click

        If ListView1.SelectedItems.Count > 0 Then
            respone = MsgBox("ต้องการ ย้าย Booking No." + ListView1.SelectedItems(0).SubItems(0).Text, MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
            If respone = 1 Then

                Dim cf As New frmeditbooking_voyage
                cf.MdiParent = Me.MdiParent
                Me.Close()
                cf.Dock = DockStyle.Fill
                cf.Show()




            End If

        Else
            MsgBox("กรุณาเลือกข้อมูลที่ต้องการลบ")
        End If





    End Sub
    Private Sub ButtonItem15_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem15.Click
        Dim respone As Object
        Dim idcontainervoyage As String
        Dim commandText3 As String



        respone = MsgBox("ต้องการ Confirm Voyage นี้ทั้งหมดใช่หรือไม่ กรุณาอย่าลืมระบุวันที่และเวลา", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")


        If respone = 1 And Trim(MaskedTextBoxAdv2.Text) <> "/  /" And Trim(MaskedTextBoxAdv3.Text) <> ":" Then


            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from borrow join ctnmain on borrow.CTNID = ctnmain.CTNMAINID join booking on  booking.BOOKINGID = borrow.BOOKID where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())




                    idcontainervoyage = mySqlReader("CTNID")
                    agentline = mySqlReader("CTNAGENT")
                    ctnname = mySqlReader("CTNSTRING")
                    If Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                        typectn = "22G1"
                    ElseIf Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                        typectn = "45G1"
                    End If

                    mysql1.Close()
                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If

                    commandText3 = "UPDATE ctnmain SET CTNSTAT ='4',CTNDATEOUT = '" & MaskedTextBoxAdv2.Text & "' where CTNMAINID = '" & idcontainervoyage & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql1

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql1.Close()


                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If
                    Try
                        mySqlCommand.Parameters.Clear()
                        mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                        mySqlCommand.Connection = mysql1
                        mySqlCommand.Parameters.AddWithValue("@rptctn", ctnname)

                        mySqlCommand.Parameters.AddWithValue("@rptstat", "ST")


                        stringDate = Split(MaskedTextBoxAdv2.Text, "-")
                        'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                        stringhour = Split(MaskedTextBoxAdv3.Text, ":")
                        'MsgBox(stringhour(0) + stringhour(1))
                        sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)



                        sumint = CDbl(sumtxt) + 100

                        mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                        mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                        mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                        mySqlCommand.Parameters.AddWithValue("@rptidctn", idcontainervoyage)
                        mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                        mySqlCommand.Parameters.AddWithValue("@rptdatetype1", MaskedTextBoxAdv2.Text)
                        mySqlCommand.ExecuteNonQuery()
                        mysql1.Close()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try





                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()
            MsgBox("Outแล้วครับ= =")
        ElseIf Trim(MaskedTextBoxAdv2.Text) = "/  /" Or Trim(MaskedTextBoxAdv3.Text) = ":" Then
            MsgBox("กรุณาระบุวันที่ให้ถูกต้อง")
        End If




    End Sub
    Private Sub ButtonItem16_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem16.Click
        Dim cf As New frmadd_booking

        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub ButtonItem17_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem17.Click
        If ListView1.SelectedItems.Count > 0 Then
            idbooking = ListView1.SelectedItems(0).SubItems(5).Text
            Dim cf As New frmedit_booking

            cf.MdiParent = Me.MdiParent
            Me.Close()
            cf.Dock = DockStyle.Fill
            cf.Show()
        Else
            MsgBox("กรุณาเลือก Booking จากตารางที่ต้องการแก้ไข")
        End If
    End Sub
    Private Sub ButtonItem18_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem18.Click


        respone = MsgBox("ยืนยันข้อมูลถูกต้อง", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then



            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select * from borrow join ctnmain on borrow.CTNID = ctnmain.CTNMAINID where BOOKID ='" & idbooking & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader
                While (mySqlReader.Read())

                    If mysql1.State = ConnectionState.Closed Then
                        mysql1.Open()
                    End If
                    Dim commandText3 As String
                    commandText3 = "UPDATE ctnmain SET CTNSTAT = '1',CTNSEALID = '',CTNWEIGHT ='0' where CTNMAINID = '" & mySqlReader("CTNMAINID") & "';"


                    Try
                        mySqlCommand1.CommandText = commandText3
                        mySqlCommand1.CommandType = CommandType.Text
                        mySqlCommand1.Connection = mysql1

                        mySqlCommand1.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql1.Close()


                End While


            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql1.Close()
            If mysql1.State = ConnectionState.Closed Then
                mysql1.Open()
            End If
            Try

                mySqlCommand1.CommandText = "DELETE FROM borrow where idborrow = '" & idbooking & "';"
                mySqlCommand1.CommandType = CommandType.Text
                mySqlCommand1.Connection = mysql1

                mySqlCommand1.ExecuteNonQuery()
                mysql1.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql1.Close()

            If mysql1.State = ConnectionState.Closed Then
                mysql1.Open()
            End If
            Try

                mySqlCommand1.CommandText = "DELETE FROM booking where BOOKINGID = '" & idbooking & "';"
                mySqlCommand1.CommandType = CommandType.Text
                mySqlCommand1.Connection = mysql1

                mySqlCommand1.ExecuteNonQuery()
                mysql1.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql1.Close()

            loadlistview1()
        End If
    End Sub
    Private Sub ButtonItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem2.Click

        Dim cf As New frmadd_gate
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub ButtonItem10_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem10.Click
        If DataGridViewX1.Rows.Count > 0 Then
            Dim cf As New frmedit_gate
            cf.MdiParent = Me.MdiParent
            Me.Close()
            cf.Dock = DockStyle.Fill
            cf.Show()
        Else
            MsgBox("กรุณาเลือกตู้ Container")
        End If



    End Sub
    Private Sub ButtonItem19_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem19.Click
        Dim respone As Object

        respone = MsgBox("ต้องการลบ ตู้ Container ตู้นี้หรือไม่ ข้อมูลตู้จะถูกกลับสถานะมาอยู่บนลาน", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")



        If DataGridViewX1.Rows.Count > 0 Then
            If respone = 1 Then
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Dim commandText3 As String
                commandText3 = "UPDATE ctnmain SET CTNSTAT = '1',CTNSEALID = '',CTNWEIGHT ='0' where CTNMAINID = '" & idcontainer & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()


                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try

                    mySqlCommand.CommandText = "DELETE FROM borrow where idborrow = '" & idborrow & "';"
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()


                Catch ex As Exception

                    MsgBox(ex.ToString)
                    Exit Sub
                End Try
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                mySqlCommand.CommandText = "Select * from borrow join ctnmain on borrow.CTNID = ctnmain.CTNMAINID where BOOKID ='" & idbooking & "' ;"
                ' mySqlCommand.CommandText = 
                mySqlCommand.Connection = mysql
                mySqlAdaptor.SelectCommand = mySqlCommand

                Try
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())




                    End While
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()

            End If


        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")
        End If
    End Sub
    Private Sub ButtonItem4_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem4.Click
        Dim cf As New frmadd_invoyage
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()
    End Sub
    Private Sub DataGridViewX1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex

            DataGridViewX1.Rows(inbtIndex).Selected = True

            idborrow = DataGridViewX1.Rows(inbtIndex).Cells(16).Value.ToString
            idcontainer = DataGridViewX1.Rows(inbtIndex).Cells(17).Value
        Catch ex As Exception

        End Try

        If e.ColumnIndex = 0 And DataGridViewX1.Rows(inbtIndex).Cells(18).Value = "1" Then

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select CTNMARKSTR from ctnmain where CTNMAINID ='" & idcontainer & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    PanelEx1.Text = mySqlReader("CTNMARKSTR")

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

        End If


        If e.ColumnIndex = 1 And DataGridViewX1.Rows(inbtIndex).Cells(19).Value = "1" Then
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select CTNMARKSEAL from ctnmain where CTNMAINID ='" & idcontainer & "';"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    PanelEx1.Text = mySqlReader("CTNMARKSEAL")

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()
        End If
    End Sub
    Private Sub Save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Save.Click
        Dim check_count As Integer = 0
        Dim commandText3 As String
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        Try
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            commandText3 = "UPDATE borrow SET COMNAME = '" & DataGridViewX1.Rows(inbtIndex).Cells(12).Value & "',CARID = '" & DataGridViewX1.Rows(inbtIndex).Cells(13).Value & "', TIMEDATE ='" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' ,  TIMEHHMM = '" & DataGridViewX1.Rows(inbtIndex).Cells(5).Value & "' , TIMEDATEIN ='" & DataGridViewX1.Rows(inbtIndex).Cells(7).Value & "', TIMEHHMMIN ='" & DataGridViewX1.Rows(inbtIndex).Cells(8).Value & "' where idborrow = '" & idborrow & "'; "


            Try
                mySqlCommand.CommandText = commandText3
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

        Catch ex As Exception

        End Try


        Try
            If DataGridViewX1.Rows(inbtIndex).Cells(6).Value = True Then
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & idcontainer & "' and rptstat ='MS' ;"
                ' mySqlCommand.CommandText = 
                mySqlCommand.Connection = mysql
                mySqlAdaptor.SelectCommand = mySqlCommand

                Try
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())

                        check_count = mySqlReader("ctncount")


                    End While
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()




                If check_count > 0 Then

                Else
                    Dim typectn As String
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    mySqlCommand.CommandText = "Select CTNSIZE,CTNAGENT from ctnmain where CTNMAINID ='" & idcontainer & "';"
                    ' mySqlCommand.CommandText = 
                    mySqlCommand.Connection = mysql
                    mySqlAdaptor.SelectCommand = mySqlCommand

                    Try
                        mySqlReader = mySqlCommand.ExecuteReader

                        While (mySqlReader.Read())
                            If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                typectn = "45G1"
                            ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                typectn = "22G1"
                            End If

                            agentline = Trim(mySqlReader("CTNAGENT"))

                        End While
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()


                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE ctnmain SET CTNSTAT ='2' where CTNMAINID = '" & idcontainer & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()









                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If


                    stringDate = Split(DataGridViewX1.Rows(inbtIndex).Cells(4).Value, "-")
                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                    stringhour = Split(DataGridViewX1.Rows(inbtIndex).Cells(5).Value, ":")
                    'MsgBox(stringhour(0) + stringhour(1))
                    sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                    mySqlCommand.Parameters.Clear()
                    Try
                        mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                        mySqlCommand.Connection = mysql
                        mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(inbtIndex).Cells(0).Value)
                        mySqlCommand.Parameters.AddWithValue("@rptstat", "MS")

                        sumint = CDbl(sumtxt) + 100

                        mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                        mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                        mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                        mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(inbtIndex).Cells(17).Value)
                        mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                        mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try


                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE borrow SET BRCHECK='1' where idborrow = '" & idborrow & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()






                End If
            End If
            check_count = 0
            If DataGridViewX1.Rows(inbtIndex).Cells(9).Value = True Then

                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & idcontainer & "' and rptstat ='FL' ;"
                ' mySqlCommand.CommandText = 
                mySqlCommand.Connection = mysql
                mySqlAdaptor.SelectCommand = mySqlCommand

                Try
                    mySqlReader = mySqlCommand.ExecuteReader

                    While (mySqlReader.Read())

                        check_count = mySqlReader("ctncount")


                    End While
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()

                If check_count > 0 Then

                Else
                    Dim typectn As String
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    mySqlCommand.CommandText = "Select CTNSIZE,CTNAGENT from ctnmain where CTNMAINID ='" & idcontainer & "';"
                    ' mySqlCommand.CommandText = 
                    mySqlCommand.Connection = mysql
                    mySqlAdaptor.SelectCommand = mySqlCommand

                    Try
                        mySqlReader = mySqlCommand.ExecuteReader

                        While (mySqlReader.Read())
                            If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                typectn = "45G1"
                            ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                typectn = "22G1"
                            End If

                            agentline = Trim(mySqlReader("CTNAGENT"))

                        End While
                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE ctnmain SET CTNSTAT ='3' where CTNMAINID = '" & idcontainer & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()


                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If


                    stringDate = Split(DataGridViewX1.Rows(inbtIndex).Cells(7).Value, "-")
                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                    stringhour = Split(DataGridViewX1.Rows(inbtIndex).Cells(8).Value, ":")
                    'MsgBox(stringhour(0) + stringhour(1))
                    sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                    mySqlCommand.Parameters.Clear()
                    mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                    mySqlCommand.Connection = mysql
                    mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(inbtIndex).Cells(0).Value)
                    mySqlCommand.Parameters.AddWithValue("@rptstat", "FL")

                    sumint = CDbl(sumtxt) + 100

                    mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                    mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                    mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                    mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(inbtIndex).Cells(17).Value)
                    mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                    mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(inbtIndex).Cells(8).Value)
                    mySqlCommand.ExecuteNonQuery()

                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE borrow SET BRCHECK1='1' where idborrow = '" & idborrow & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()




                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE borrow SET BRBILLNAME='" & DataGridViewX1.Rows(inbtIndex).Cells(14).Value & "' where idborrow = '" & idborrow & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNSTRING ='" & DataGridViewX1.Rows(inbtIndex).Cells(0).Value.ToString & "' , CTNSEALID = '" & DataGridViewX1.Rows(inbtIndex).Cells(1).Value.ToString & "', CTNWEIGHT ='" & DataGridViewX1.Rows(inbtIndex).Cells(11).Value.ToString & "' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Public Sub loadlistview1()
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select BNO,BSHIPNAME,BFORWARDERNAME,BCONFIRMTIME,BCONFIRMDAY,BCTNTYPE,BCTNNO,BNICKAGENT,BOOKINGID,BCTNTYPE,BCTNNO,billbooking from booking where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("BNO"))


                    If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BSHIPNAME"))
                    End If

                    If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    Else

                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    End If

                    If mySqlReader("BCONFIRMDAY") Is DBNull.Value And mySqlReader("BCONFIRMTIME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BCONFIRMDAY") + " @" + mySqlReader("BCONFIRMTIME"))
                    End If

                    .SubItems.Add(mySqlReader("BCTNNO") + " X " + mySqlReader("BCTNTYPE"))
                    .SubItems.Add(mySqlReader("BOOKINGID"))


                    If mySqlReader("BCTNTYPE") = "40'HC" Then
                        sumctn40 += CInt(mySqlReader("BCTNNO"))
                    ElseIf mySqlReader("BCTNTYPE") = "20'GP" Then
                        sumctn20 += CInt(mySqlReader("BCTNNO"))
                    End If

                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))


                End With



            End While


            TextBox1.Text = sumctn40.ToString + "  40'HC"
            TextBox2.Text = sumctn20.ToString + "  20'GP"
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub
    Private Sub ButtonItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem6.Click
        Dim nextform As frmsearch_voyctn = New frmsearch_voyctn
        nextform.Show()
    End Sub
    Private Sub DataGridViewX1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellEndEdit
        Dim first As String = ""
        Dim sec As String = ""
        Dim third As String = ""

        inbtIndex = e.RowIndex
        Dim full As String = ""
        Dim full1 As String = ""

        If e.ColumnIndex = 4 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(4).Value) = 8 Then

                full = DataGridViewX1.Rows(inbtIndex).Cells(4).Value
                first = full.Substring(full.Length - 4)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 4)
                sec = full1.Substring(full1.Length - 2)
                third = Microsoft.VisualBasic.Left(full1, full1.Length - 2)

                DataGridViewX1.Rows(inbtIndex).Cells(4).Value = third + "-" + sec + "-" + first
            End If
        End If
        If e.ColumnIndex = 7 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(7).Value) = 8 Then
                full = DataGridViewX1.Rows(inbtIndex).Cells(7).Value
                first = full.Substring(full.Length - 4)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 4)
                sec = full1.Substring(full1.Length - 2)
                third = Microsoft.VisualBasic.Left(full1, full1.Length - 2)

                DataGridViewX1.Rows(inbtIndex).Cells(7).Value = third + "-" + sec + "-" + first
            End If
        End If

        If e.ColumnIndex = 5 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(5).Value) = 4 Then
                full = DataGridViewX1.Rows(inbtIndex).Cells(5).Value
                first = full.Substring(full.Length - 2)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 2)
                DataGridViewX1.Rows(inbtIndex).Cells(5).Value = full1 + ":" + first
            End If
        End If

        If e.ColumnIndex = 8 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(8).Value) = 4 Then
                full = DataGridViewX1.Rows(inbtIndex).Cells(8).Value
                first = full.Substring(full.Length - 2)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 2)
                DataGridViewX1.Rows(inbtIndex).Cells(8).Value = full1 + ":" + first
            End If
        End If

        If e.ColumnIndex = 13 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(13).Value) = 6 Then
                full = DataGridViewX1.Rows(inbtIndex).Cells(13).Value
                first = full.Substring(full.Length - 4)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 4)
                DataGridViewX1.Rows(inbtIndex).Cells(12).Value = full1 + "-" + first
            End If

            If Len(DataGridViewX1.Rows(inbtIndex).Cells(13).Value) = 13 Then
                Dim slipt() As String
                Dim value1 As String
                slipt = Split(DataGridViewX1.Rows(inbtIndex).Cells(13).Value, "/")

                full = slipt(0)
                first = full.Substring(full.Length - 4)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 4)
                value1 = full1 + "-" + first

                full = slipt(1)
                first = full.Substring(full.Length - 4)
                full1 = Microsoft.VisualBasic.Left(full, full.Length - 4)
                value1 += "/" + full1 + "-" + first
                DataGridViewX1.Rows(inbtIndex).Cells(13).Value = value1
            End If


        End If

        If e.ColumnIndex = 11 Then
            If Len(DataGridViewX1.Rows(inbtIndex).Cells(11).Value) > 0 Then
                Dim e1 As Expression
                Try
                    e1 = New Expression(DataGridViewX1.Rows(inbtIndex).Cells(11).Value.ToString)
                    DataGridViewX1.Rows(inbtIndex).Cells(11).Value = CDbl(CInt(e1.Evaluate().ToString)).ToString("#,###.00")
                Catch ex As Exception

                End Try

            End If
        End If



    End Sub
    Private Sub DataGridViewX1_CellMouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridViewX1.CellMouseDown
        If e.ColumnIndex = 0 Then
            idcontainer = DataGridViewX1.Rows(e.RowIndex).Cells(17).Value
            Dim rowClicked As DataGridView.HitTestInfo = DataGridViewX1.HitTest(e.X, e.Y)
            'Select Right Clicked Row if its not the header row
            If e.Button = Windows.Forms.MouseButtons.Right AndAlso e.RowIndex > -1 Then
                'Clear any currently sellected rows
                DataGridViewX1.CurrentCell = DataGridViewX1.Rows(e.RowIndex).Cells(e.ColumnIndex)

                ContextMenuStrip1.Show(DataGridViewX1, New Point(e.RowIndex, e.ColumnIndex))

            End If
        End If
        If e.ColumnIndex = 1 Then
            idcontainer = DataGridViewX1.Rows(e.RowIndex).Cells(17).Value
            Dim rowClicked As DataGridView.HitTestInfo = DataGridViewX1.HitTest(e.X, e.Y)
            'Select Right Clicked Row if its not the header row
            If e.Button = Windows.Forms.MouseButtons.Right AndAlso e.RowIndex > -1 Then
                'Clear any currently sellected rows
                DataGridViewX1.CurrentCell = DataGridViewX1.Rows(e.RowIndex).Cells(e.ColumnIndex)

                ContextMenuStrip2.Show(DataGridViewX1, New Point(e.RowIndex, e.ColumnIndex))

            End If
        End If

    End Sub
    Private Sub DataGridViewX1_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX1.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub
    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click


        If DataGridViewX1.Rows.Count > 0 Then
            For i = 0 To DataGridViewX1.Rows.Count - 1

                Dim check_count As Integer = 0
                Dim commandText3 As String
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE borrow SET COMNAME = '" & DataGridViewX1.Rows(i).Cells(12).Value & "',CARID = '" & DataGridViewX1.Rows(i).Cells(13).Value & "', TIMEDATE ='" & DataGridViewX1.Rows(i).Cells(4).Value & "' ,  TIMEHHMM = '" & DataGridViewX1.Rows(i).Cells(5).Value & "' , TIMEDATEIN ='" & DataGridViewX1.Rows(i).Cells(7).Value & "', TIMEHHMMIN ='" & DataGridViewX1.Rows(i).Cells(8).Value & "' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                If DataGridViewX1.Rows(i).Cells(6).Value = False Then


                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE borrow SET BRCHECK='0' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If


                    mySqlCommand.CommandText = "DELETE FROM rptwhl where rptidctn = '" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='MS';"
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()


                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If

                    commandText3 = "UPDATE ctnmain SET CTNSTAT ='1' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                End If
                If DataGridViewX1.Rows(i).Cells(9).Value = False Then

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    If DataGridViewX1.Rows(i).Cells(9).Value = False And DataGridViewX1.Rows(i).Cells(6).Value = False Then

                        commandText3 = "UPDATE borrow SET BRCHECK1='0'  , BRCHECK ='0' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "
                    ElseIf DataGridViewX1.Rows(i).Cells(9).Value = False And DataGridViewX1.Rows(i).Cells(6).Value = True Then


                        commandText3 = "UPDATE borrow SET BRCHECK1='0' ,BRCHECK='1' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "

                    End If



                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If


                    mySqlCommand.CommandText = "DELETE FROM rptwhl where rptidctn = '" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='FL';"
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()


                    mysql.Close()

                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    If DataGridViewX1.Rows(i).Cells(9).Value = False And DataGridViewX1.Rows(i).Cells(6).Value = False Then

                        commandText3 = "UPDATE ctnmain SET CTNSTAT ='1' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "
                    ElseIf DataGridViewX1.Rows(i).Cells(9).Value = False And DataGridViewX1.Rows(i).Cells(6).Value = True Then


                        commandText3 = "UPDATE ctnmain SET CTNSTAT ='2' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "

                    End If



                    Try
                        mySqlCommand.CommandText = commandText3
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try
                    mysql.Close()


                End If
                Try
                    If DataGridViewX1.Rows(i).Cells(6).Value = True Then
                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='MS' ;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand.Connection = mysql
                        mySqlAdaptor.SelectCommand = mySqlCommand

                        Try
                            mySqlReader = mySqlCommand.ExecuteReader

                            While (mySqlReader.Read())

                                check_count = mySqlReader("ctncount")


                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()




                        If check_count > 0 Then

                        Else
                            Dim typectn As String
                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If
                            mySqlCommand.CommandText = "Select * from ctnmain where CTNMAINID ='" & DataGridViewX1.Rows(i).Cells(17).Value & "';"
                            ' mySqlCommand.CommandText = 
                            mySqlCommand.Connection = mysql
                            mySqlAdaptor.SelectCommand = mySqlCommand

                            Try
                                mySqlReader = mySqlCommand.ExecuteReader

                                While (mySqlReader.Read())
                                    If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                        typectn = "45G1"
                                    ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                        typectn = "22G1"
                                    End If

                                    agentline = Trim(mySqlReader("CTNAGENT"))

                                End While
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()


                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE ctnmain SET CTNSTAT ='2' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()









                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If


                            stringDate = Split(DataGridViewX1.Rows(i).Cells(4).Value, "-")
                            'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                            stringhour = Split(DataGridViewX1.Rows(i).Cells(5).Value, ":")
                            'MsgBox(stringhour(0) + stringhour(1))
                            sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                            mySqlCommand.Parameters.Clear()
                            Try
                                mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                                mySqlCommand.Connection = mysql
                                mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(i).Cells(0).Value)
                                mySqlCommand.Parameters.AddWithValue("@rptstat", "MS")

                                sumint = CDbl(sumtxt) + 100

                                mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                                mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                                mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                                mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(i).Cells(17).Value)
                                mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                                mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(i).Cells(4).Value)
                                mySqlCommand.ExecuteNonQuery()
                                mysql.Close()
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try


                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE borrow SET BRCHECK='1' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()






                        End If
                    End If
                    check_count = 0
                    If DataGridViewX1.Rows(i).Cells(9).Value = True Then

                        If mysql.State = ConnectionState.Closed Then
                            mysql.Open()
                        End If
                        mySqlCommand.CommandText = "Select COUNT(rptidctn) as ctncount from rptwhl where rptidctn ='" & DataGridViewX1.Rows(i).Cells(17).Value & "' and rptstat ='FL' ;"
                        ' mySqlCommand.CommandText = 
                        mySqlCommand.Connection = mysql
                        mySqlAdaptor.SelectCommand = mySqlCommand

                        Try
                            mySqlReader = mySqlCommand.ExecuteReader

                            While (mySqlReader.Read())

                                check_count = mySqlReader("ctncount")


                            End While
                        Catch ex As Exception
                            MsgBox(ex.ToString)
                        End Try
                        mysql.Close()

                        If check_count > 0 Then

                        Else
                            Dim typectn As String
                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If
                            mySqlCommand.CommandText = "Select * from ctnmain where CTNMAINID ='" & DataGridViewX1.Rows(i).Cells(17).Value & "';"
                            ' mySqlCommand.CommandText = 
                            mySqlCommand.Connection = mysql
                            mySqlAdaptor.SelectCommand = mySqlCommand

                            Try
                                mySqlReader = mySqlCommand.ExecuteReader

                                While (mySqlReader.Read())
                                    If Trim(mySqlReader("CTNSIZE")) = "40'HC" Then
                                        typectn = "45G1"
                                    ElseIf Trim(mySqlReader("CTNSIZE")) = "20'GP" Then
                                        typectn = "22G1"
                                    End If

                                    agentline = Trim(mySqlReader("CTNAGENT"))

                                End While
                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()

                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE ctnmain SET CTNSTAT ='3' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()


                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If


                            stringDate = Split(DataGridViewX1.Rows(i).Cells(7).Value, "-")
                            'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))
                            stringhour = Split(DataGridViewX1.Rows(i).Cells(8).Value, ":")
                            'MsgBox(stringhour(0) + stringhour(1))
                            sumtxt = stringDate(2) + stringDate(1) + stringDate(0) + stringhour(0) + stringhour(1)
                            mySqlCommand.Parameters.Clear()
                            mySqlCommand.CommandText = "insert into rptwhl ( rptctn, rptstat, rptctndate, rptctndatetpe,rpttype,rptidctn,rptagent,rptdatetype1) values (@rptctn,@rptstat,@rptctndate,@rptctndatetpe,@rpttype,@rptidctn,@rptagent,@rptdatetype1)"
                            mySqlCommand.Connection = mysql
                            mySqlCommand.Parameters.AddWithValue("@rptctn", DataGridViewX1.Rows(i).Cells(0).Value)
                            mySqlCommand.Parameters.AddWithValue("@rptstat", "FL")

                            sumint = CDbl(sumtxt) + 100

                            mySqlCommand.Parameters.AddWithValue("@rptctndate", sumtxt + "00")
                            mySqlCommand.Parameters.AddWithValue("@rptctndatetpe", sumint.ToString + "00")
                            mySqlCommand.Parameters.AddWithValue("@rpttype", typectn)
                            mySqlCommand.Parameters.AddWithValue("@rptidctn", DataGridViewX1.Rows(i).Cells(17).Value)
                            mySqlCommand.Parameters.AddWithValue("@rptagent", agentline)
                            mySqlCommand.Parameters.AddWithValue("@rptdatetype1", DataGridViewX1.Rows(i).Cells(7).Value)
                            mySqlCommand.ExecuteNonQuery()

                            mysql.Close()

                            If mysql.State = ConnectionState.Closed Then
                                mysql.Open()
                            End If

                            commandText3 = "UPDATE borrow SET BRCHECK1='1' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                            Try
                                mySqlCommand.CommandText = commandText3
                                mySqlCommand.CommandType = CommandType.Text
                                mySqlCommand.Connection = mysql

                                mySqlCommand.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.ToString)
                            End Try
                            mysql.Close()




                        End If
                    End If

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If

                commandText3 = "UPDATE borrow SET BRBILLNAME='" & DataGridViewX1.Rows(i).Cells(14).Value & "' where idborrow = '" & DataGridViewX1.Rows(i).Cells(16).Value & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()

                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If

                commandText3 = "UPDATE ctnmain SET CTNSTRING ='" & DataGridViewX1.Rows(i).Cells(0).Value & "' , CTNSEALID = '" & DataGridViewX1.Rows(i).Cells(1).Value & "', CTNWEIGHT ='" & DataGridViewX1.Rows(i).Cells(11).Value & "' , CTNVOYS ='" & txt_voyage.Text & "' where CTNMAINID = '" & DataGridViewX1.Rows(i).Cells(17).Value & "'; "


                Try
                    mySqlCommand.CommandText = commandText3
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
                mysql.Close()



            Next




        End If
        MsgBox("Save(บันทึกเสร็จสิ้น)")



    End Sub
    Private Sub txt_voyage_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txt_voyage.KeyDown
        If e.KeyCode = Keys.Enter Then


            frmsearch_voyage1.idvoyage = txt_voyage.Text

            ListView1.Items.Clear()
            DataGridViewX1.Rows.Clear()
            mysql.Close()
            txt_voyage.Text = Format(frmsearch_voyage1.idvoyage, "000")

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select VSSNAM,VSSMASTER,VSSNATION,VOYDATESS,VOYDATEES from voyage join vesmain on voyage.VOYVESIDN = vesmain.VESMAINID where VOYAGEID ='" & frmsearch_voyage1.idvoyage & "' ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    txt_vessel.Text = mySqlReader("VSSNAM")
                    txt_namemaster.Text = CStr(mySqlReader("VSSMASTER"))
                    txt_nationality.Text = mySqlReader("VSSNATION")
                    txt_eta.Text = CStr(mySqlReader("VOYDATESS"))
                    txt_eta_penang.Text = mySqlReader("VOYDATEES")

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()



            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select BNO,BSHIPNAME,BFORWARDERNAME,BCONFIRMTIME,BCONFIRMDAY,BCTNTYPE,BCTNNO,BNICKAGENT,BOOKINGID,BCTNTYPE,BCTNNO,billbooking  from booking left join booking_bl on booking_bl.idbooking_bl =  booking.BINV   where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' order by  BCTNTYPE ASC , FIELD( BFORWARDERNAME ,'v.i.m','psk','queen','3gshipping' ,'baolidor','kvm','j.mac') DESC;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            sumctn40 = 0
            sumctn20 = 0
            Try
                mySqlReader = mySqlCommand.ExecuteReader
                ListView1.Items.Clear()
                While (mySqlReader.Read())


                    With ListView1.Items.Add(mySqlReader("BNO"))


                        If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                            .SubItems.Add("-")
                        Else

                            .SubItems.Add(mySqlReader("BSHIPNAME"))
                        End If

                        If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                            .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                        Else

                            .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                        End If

                        If mySqlReader("BCONFIRMDAY") Is DBNull.Value And mySqlReader("BCONFIRMTIME") Is DBNull.Value Then
                            .SubItems.Add("-")
                        Else

                            .SubItems.Add(mySqlReader("BCONFIRMDAY") + " @" + mySqlReader("BCONFIRMTIME"))
                        End If
                        Dim array() As String
                        array = Split(mySqlReader("BCTNTYPE"), "'")
                        .SubItems.Add(mySqlReader("BCTNNO") + " X " + array(0) + "' " + mySqlReader("BNICKAGENT"))
                        .SubItems.Add(mySqlReader("BOOKINGID"))


                        If mySqlReader("BCTNTYPE") = "40'HC" Then
                            sumctn40 += CInt(mySqlReader("BCTNNO"))
                        ElseIf mySqlReader("BCTNTYPE") = "20'GP" Then
                            sumctn20 += CInt(mySqlReader("BCTNNO"))
                        End If
                        If mySqlReader("billbooking") Is DBNull.Value Then
                        Else
                            .SubItems.Add(mySqlReader("billbooking"))
                        End If

                        'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))


                    End With



                End While


                TextBox1.Text = sumctn40.ToString + "  40'"
                TextBox2.Text = sumctn20.ToString + "  20'"
            Catch ex As Exception
                'MsgBox(ex.ToString)
            End Try
            mysql.Close()

        End If
    End Sub
    Private Sub ButtonItem20_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem20.Click
        Dim nextform As frmmove_ctn = New frmmove_ctn
        nextform.Show()
    End Sub
    Private Sub Mark_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Mark.Click

        Dim StatusDate As String
        Dim commandText3 As String
        StatusDate = Microsoft.VisualBasic.InputBox("Message")

        If StatusDate.Length = 0 Then
        Else


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            commandText3 = "UPDATE ctnmain SET CTNMARKSTR ='" & StatusDate & "',CTNMARKSTRBOO ='1' where CTNMAINID = '" & idcontainer & "'; "


            Try
                mySqlCommand.CommandText = commandText3
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()



        End If

        saveall()
        loaddatagrid()
    End Sub
    Private Sub mark1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles mark1.Click

        Dim StatusDate As String
        Dim commandText3 As String
        StatusDate = Microsoft.VisualBasic.InputBox("Message")

        If StatusDate.Length = 0 Then
        Else


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            commandText3 = "UPDATE ctnmain SET CTNMARKSEAL ='" & StatusDate & "',CTNMARKSEALBOO ='1' where CTNMAINID = '" & idcontainer & "'; "


            Try
                mySqlCommand.CommandText = commandText3
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()



        End If

        saveall()
        loaddatagrid()
    End Sub
    Private Sub unmark_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles unmark.Click
        Dim commandtext3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandtext3 = "UPDATE ctnmain SET CTNMARKSTR ='',CTNMARKSTRBOO ='0' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()
    End Sub
    Private Sub unmark1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles unmark1.Click

        Dim StatusDate As String
        Dim commandText3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNMARKSEAL ='',CTNMARKSEALBOO ='0' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()


    End Sub
    Private Sub upgrade_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles upgrade.Click
        Dim commandText3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNUPGRADE ='U' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()
    End Sub
    Private Sub Upgrade1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upgrade1.Click
        Dim commandText3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNUPGRADE ='U' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()
    End Sub
    Private Sub RibbonBar3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Normal.Click
        Dim commandText3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNUPGRADE ='N' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()
    End Sub
    Private Sub Normal1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Normal1.Click

        Dim commandText3 As String

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNUPGRADE ='N' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        saveall()
        loaddatagrid()
    End Sub
    Private Sub ยายตToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ยายตToolStripMenuItem.Click
        Dim nextform As frmmove_ctn = New frmmove_ctn
        nextform.Show()
    End Sub
    Private Sub ยายตToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ยายตToolStripMenuItem1.Click
        Dim nextform As frmmove_ctn = New frmmove_ctn
        nextform.Show()
    End Sub
    Private Sub ListView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDown
        If e.Button = MouseButtons.Right Then
            ContextMenuStrip3.Show(ListView1, New Point(e.X, e.Y))

            'code here to GetItemAt. I am unsure as to what you mean by this?
        End If
    End Sub
    Private Sub ToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripMenuItem1.Click
        If ListView1.SelectedItems.Count > 0 Then
            idbooking = ListView1.SelectedItems(0).SubItems(5).Text
            Dim cf As New frmedit_booking

            cf.MdiParent = Me.MdiParent
            Me.Close()
            cf.Dock = DockStyle.Fill
            cf.Show()
        Else
            MsgBox("กรุณาเลือก Booking จากตารางที่ต้องการแก้ไข")
        End If


    End Sub
    Private Sub ยายBookingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ยายBookingToolStripMenuItem.Click
        If ListView1.SelectedItems.Count > 0 Then
            respone = MsgBox("ต้องการ ย้าย Booking No." + ListView1.SelectedItems(0).SubItems(0).Text, MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
            If respone = 1 Then

                Dim cf As New frmeditbooking_voyage
                cf.MdiParent = Me.MdiParent
                Me.Close()
                cf.Dock = DockStyle.Fill
                cf.Show()




            End If

        Else
            MsgBox("กรุณาเลือกข้อมูลที่ต้องการลบ")
        End If


    End Sub

    Private Sub ButtonItem22_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem22.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"
        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Try
                Dim t = New Thread(New ThreadStart(AddressOf excelReportETD))
                t.Start()
                CircularProgress1.IsRunning = True
            Catch ex As Exception

            End Try
        End If
    End Sub

    Private Sub excelReportETD()

        Dim pathExcel As String
        Dim count As Integer = 7
        Dim dateout As String = ""
        Dim ctntype As String = ""
        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Name = "V.S" + txt_voyage.Text
        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape


            Try
                '.Shapes.AddPicture("\\ykpserver55\DB\movement.png", False, True, 1, 1, 150, 80)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 3.57
            .Range("B1").ColumnWidth = 20.29
            .Range("C1").ColumnWidth = 47.43
            .Range("D1").ColumnWidth = 80.57
            .Range("E1").ColumnWidth = 40.71
            .Range("F1").ColumnWidth = 40.29
            .Range("G1").ColumnWidth = 29.14
            .Range("H1").ColumnWidth = 27
            .Range("I1").ColumnWidth = 11.57
            .Range("J1").ColumnWidth = 23
            .Range("K1").ColumnWidth = 20.57
            .Range("L1").ColumnWidth = 30.14
    

            .Range("A1:M500").Font.Name = "Arial"
            .Range("A1:M500").Font.Size = 12

            .Rows("1:1").rowheight = 38
            .Rows("2:500").rowheight = 27


            With .Range("D1:F1")
                .Merge()
                .Font.Size = 20
                .Value = "LOADING LIST V.S" + txt_voyage.Text
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("B2:C2")
                .Merge()
                .Value = "YKP OCEAN LINE CO., LTD."
                .Font.Size = 14
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("B3:C3")
                .Merge()
                .Value = "87/1 M.5 T.Boonumron, Kantang, Trang, Thailand "
                .Font.Size = 14
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A5:A6")
                .Merge()
                .Value = "No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("B5:B6")
                .Merge()
                .Value = "ETD from kantang "
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("C5:C6")
                .Merge()
                .Value = "Name of Cargo Owner"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("D5:D6")
                .Merge()
                .Value = "Consingnee"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("E5:E6")
                .Merge()
                .Value = "Incoterm "
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
                .Font.Underline = True

            End With

            With .Range("F5:F6")
                .Merge()
                .WrapText = True
                .Value = "From " + Environment.NewLine + "Kantang Thailand " + Environment.NewLine + "to Penang"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("G5:G6")
                .Merge()
                .WrapText = True
                .Value = "Conveyance"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("H5:H6")
                .Merge()
                .WrapText = True
                .Value = "Invoice Value"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I5:I6")
                .Merge()
                .WrapText = True
                .Value = "Insured" + Environment.NewLine + "Value" + Environment.NewLine + "(110%)"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            With .Range("J5:J6")
                .Merge()
                .WrapText = True
                .Value = "Number of container"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            With .Range("K5:K6")
                .Merge()
                .WrapText = True
                .Value = "Booking No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("L5:L6")
                .Merge()
                .WrapText = True
                .Value = "Remark"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            .Range("A5:L6").Borders.Weight = 2 ' xlThin

            mysql1.Close()
            If mysql1.State = ConnectionState.Closed Then
                mysql1.Open()
            End If
            mySqlCommand.CommandText = "Select VOYDATEES,BSHIPNAME,conname,BINCOTERM,VOYVESNAMES,place,BINVOICE,BNO,BCTNNO,BCTNTYPE,BREMARK from voyage join booking on voyage.VOYAGEID = booking.BVOYAGE left join booking_bl on booking.BOOKINGID = booking_bl.booking_id where BCHECKLOAD ='True' and BVOYAGE = '" & txt_voyage.Text & "' order by  conname ;"
            ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql1
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader
                Dim number As Integer = 1
                count = 7
                Dim sumctn As Integer = 0
                While (mySqlReader.Read())

                    .Range("A" + count.ToString + ":L" + count.ToString).Borders.Weight = 2 ' xlThin

                    With .Range("A" + count.ToString + ":A" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = number

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    number += 1
                    With .Range("B" + count.ToString + ":B" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("VOYDATEES")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        dateout = mySqlReader("VOYDATEES")
                    End With

                    With .Range("C" + count.ToString + ":C" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BSHIPNAME")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("D" + count.ToString + ":D" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("conname")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("E" + count.ToString + ":E" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BINCOTERM")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With

                    With .Range("F" + count.ToString + ":F" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("VOYVESNAMES")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With


                    With .Range("G" + count.ToString + ":G" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("place")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With

                    With .Range("H" + count.ToString + ":H" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BINVOICE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("K" + count.ToString + ":K" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BNO")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    With .Range("J" + count.ToString + ":J" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BCTNNO") + " X " + mySqlReader("BCTNTYPE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                    End With
                    sumctn += CInt(mySqlReader("BCTNNO"))
                    ctntype = mySqlReader("BCTNTYPE")
                    With .Range("L" + count.ToString + ":L" + count.ToString)
                        .NumberFormat = "@"
                        .Merge()
                        .Value = mySqlReader("BREMARK")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                    End With
                    count += 1
                End While




                count += 1

                With .Range("J" + count.ToString + ":J" + count.ToString)
                    .NumberFormat = "@"
                    .Merge()
                    .Value = sumctn.ToString + " X " + ctntype.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleDouble

                End With
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try




        End With

        Try
            excelbooks.SaveAs(pathExcel.ToString + "\Loading_list_V.S" + txt_voyage.Text + "_ETD_" + dateout + ".xlsx")

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next
            MsgBox("Report Complete")


        Catch ex As Exception

            excelbooks.Close()
            excelapp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp)
            excelbooks = Nothing
            excelsheets = Nothing
            excelapp = Nothing
            Dim proc As System.Diagnostics.Process

            For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
                proc.Kill()
            Next


        End Try
        showResult(200)
        CircularProgress1.IsRunning = False

    End Sub

    Private Sub ButtonItem21_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem21.Click
        Dim nextform As frmrpt_loading = New frmrpt_loading
        nextform.Show()
    End Sub

    Private Sub TBABookingToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TBABookingToolStripMenuItem.Click
        If ListView1.SelectedItems.Count > 0 Then
            If dateconfirm.Text <> "" AndAlso MaskedTextBoxAdv1.Text <> "" Then
                mysql.Close()
                If mysql.State = ConnectionState.Closed Then
                    mysql.Open()
                End If
                Try
                    Dim commandText2 As String
                    commandText2 = "UPDATE booking SET BCONFIRMDAY = 'TBA',BCONFIRMTIME =' '  WHERE BOOKINGID = '" & ListView1.SelectedItems(0).SubItems(5).Text & "'; "
                    mySqlCommand.CommandText = commandText2
                    mySqlCommand.CommandType = CommandType.Text
                    mySqlCommand.Connection = mysql

                    mySqlCommand.ExecuteNonQuery()
                    mysql.Close()

                    MsgBox("แก้ไขข้อเรียบร้อย")

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End If


        Else
            MsgBox("กรุณาเลือก Booking เพื่อทำการ Comfirm วัน Close date")
        End If
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select BNO,BSHIPNAME,BFORWARDERNAME,BCONFIRMTIME,BCONFIRMDAY,BCTNTYPE,BCTNNO,BNICKAGENT,BOOKINGID,BCTNTYPE,BCTNNO,billbooking  from booking left join booking_bl on booking_bl.idbooking_bl =  booking.BINV where BVOYAGE ='" & frmsearch_voyage1.idvoyage & "' order by  BCTNTYPE ASC , FIELD( BFORWARDERNAME ,'v.i.m','psk','queen','3gshipping' ,'baolidor','kvm','j.mac') DESC;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())


                With ListView1.Items.Add(mySqlReader("BNO"))


                    If mySqlReader("BSHIPNAME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BSHIPNAME"))
                    End If

                    If mySqlReader("BFORWARDERNAME") Is DBNull.Value Then
                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    Else

                        .SubItems.Add(mySqlReader("BFORWARDERNAME"))
                    End If

                    If mySqlReader("BCONFIRMDAY") Is DBNull.Value And mySqlReader("BCONFIRMTIME") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("BCONFIRMDAY") + " @" + mySqlReader("BCONFIRMTIME"))
                    End If
                    .SubItems.Add(mySqlReader("BCTNNO") + " X " + mySqlReader("BCTNTYPE") + "   " + mySqlReader("BNICKAGENT"))
                    .SubItems.Add(mySqlReader("BOOKINGID"))


                    If mySqlReader("BCTNTYPE") = "40'HC" Then
                        sumctn40 += CInt(mySqlReader("BCTNNO"))
                    ElseIf mySqlReader("BCTNTYPE") = "20'GP" Then
                        sumctn20 += CInt(mySqlReader("BCTNNO"))
                    End If
                    If mySqlReader("billbooking") Is DBNull.Value Then
                    Else
                        .SubItems.Add(mySqlReader("billbooking"))
                    End If
                    'MsgBox(stringDate(0) + stringDate(1) + stringDate(2))


                End With


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()



    End Sub

    Private Sub Label7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label7.Click

    End Sub

    Private Sub ButtonItem23_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonItem23.Click

    End Sub
End Class